package connect;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

	public class Conne {
		private final String driver = "com.mysql.jdbc.Driver";
		final String URL = "jdbc:mysql://127.0.0.1:3306/cxlm";
		final String UNAME = "root";
		final String PWD = "lm13190492195";
		Connection conn=null;
		public Conne(){
			try{
				Class.forName(driver).newInstance();
				conn = DriverManager.getConnection(URL,UNAME,PWD);
			}catch (InstantiationException e) {
				e.printStackTrace();
			}catch (IllegalAccessException e) {
				e.printStackTrace();
			}catch (ClassNotFoundException e) {
				e.printStackTrace();
			}catch (SQLException e){
				e.printStackTrace();
				}
			}
		
		public void insertPerson(String name,int idNum){
			String sql = "insert into person values('"+name+"',"+idNum+")";
			System.out.println(sql);
			try {
				PreparedStatement pre = conn.prepareStatement(sql);
				pre.execute();
			}catch (SQLException e){
				e.printStackTrace();
			}
		}
		
		public void insertCon(int pnum,int lnum){
			String sql = "insert into connect values("+pnum+","+lnum+")";
			System.out.println(sql);
			try {
				PreparedStatement pre = conn.prepareStatement(sql);
				pre.execute();
			}catch (SQLException e){
				e.printStackTrace();
			}
		}
		
		 public String selPL(String pName)  
		    {  
		        String sql = "select l_name from class where l_id=(select l_id from connect where s_id=(select id from person where name='"+pName+"'));";   
		        try{
		        	if(conn==null){
		        		 System.out.println("conn is null");
		        	}else{
					PreparedStatement ps = conn.prepareStatement(sql);
					ResultSet rs = ps.executeQuery();
					while(rs.next()){
						sql = rs.getString(1);
					}
		        	}
		        }
		        catch (SQLException e)  
		        {  
		            e.printStackTrace();  
		        }  
		        return sql;  
		    }
		 
		 public String selLP(String lName)  
		    {  
		        String sql = "select l_name from class where l_id=(select l_id from connect where s_id=(select id from person where name='"+lName+"'));";   
		        try{
		        	if(conn==null){
		        		 System.out.println("conn is null");
		        	}else{
					PreparedStatement ps = conn.prepareStatement(sql);
					ResultSet rs = ps.executeQuery();
					while(rs.next()){
						sql = rs.getString(1);
					}
					}
		        }
		        catch (SQLException e)  
		        {  
		            e.printStackTrace();  
		        }  
		        return sql;  
		    }

}
